/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CSISL4pk;

import javax.swing.JInternalFrame;
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;
/**
 *
 * @author Josh
 */
public class HomePageFrame extends JInternalFrame{
    static int openFrameCount = 0;
    static final int xOffset = 10, yOffset = 10;
    
    ResultSet rs;
    
    JButton loadCaseButton = new JButton();
    JButton createCaseButton = new JButton();
    JButton findCaseButton = new JButton();
    JButton statsButton = new JButton();
    JButton reportButton = new JButton();
    JLabel openCasesLabel = new JLabel();
    JLabel openCaseDispNumLabel = new JLabel();
    JLabel caseCountLabel = new JLabel();
    JLabel caseCountDispNumLabel = new JLabel();
    JLabel loadedCaseNumLabel = new JLabel();
    JLabel loadedCaseNumDispLabel = new JLabel();
    JLabel lastLoginLabel = new JLabel();
    JLabel lastLoginDispLabel = new JLabel();
       
    //query results
    int openCasesCount;
    int totalCasesCount;
    int loadedCaseNum;
    
    
    JPanel homePane = new JPanel();
    
    public HomePageFrame(int InvID)
    {
        super("Homepage" + (++openFrameCount), 
              false, //resizable
              true, //closable
              false, //maximizable
              true);//iconifiable
        
        //add GUI
        //adds container to the frame
        add(homePane);
        homePane.setLayout(null);
        setContentPane(homePane);
        //add buttons
        homePane.add(loadCaseButton);
        loadCaseButton.setText("Load Case");
        loadCaseButton.setBounds(60, 50, 130, 30);
        
        homePane.add(createCaseButton);
        createCaseButton.setText("Create Case");
        createCaseButton.setBounds(60, 170, 130, 30);
        
        homePane.add(findCaseButton);
        findCaseButton.setText("Find Case");
        findCaseButton.setBounds(60, 110, 130, 30);
        
        homePane.add(statsButton);
        statsButton.setText("Logout");
        statsButton.setBounds(60, 290, 130, 30);
        
        homePane.add(reportButton);
        reportButton.setText("Reports");
        reportButton.setBounds(60, 230, 130, 30);
        
        //select max(lastLoginDate) from session where id = invID => LastLoginDateTime
        //select count(*) from cases where status = open  and (datediff(NOW(),lastUpdatedTime)/365) < 1  => YTD open cases count
        //select count(*) from cases where status = open and invId = invID => total open cases for investigator
        try
        {

          // create our mysql database connection
          String myDriver = "com.mysql.jdbc.Driver";
          String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl?allowMultiQueries=true";
          Class.forName(myDriver);
          Connection conn = DriverManager.getConnection(myUrl, "csisaltlake","cs2450");

          // our SQL SELECT query. 
          // if you only need a few columns, specify them by name instead of using "*"
          String query = "select count(*) as ytd from cases where status = 'open'  and (datediff(NOW(),lastUpdateTime)/365) < 1";//YTD
          
          String q3 = "select count(*) as invOpen from cases where status = 'open'  and inv_ID ="+InvID;
          
          // create the java statement
          Statement st = conn.createStatement();
          //Statement st2 = conn.createStatement();
          Statement st3 = conn.createStatement();
          // execute the query, and get a java resultset
          ResultSet rs = st.executeQuery(query);
          
          ResultSet rs3 = st3.executeQuery(q3);
          // iterate through the java resultset
          while (rs.next())
          {
            //int id = rs.getInt("id");
            totalCasesCount = rs.getInt("ytd");
            
            // print the results
            System.out.println();
            System.out.format("%s", totalCasesCount);
          }
              

          while (rs3.next())
          {
              openCasesCount = rs3.getInt("invOpen");
              System.out.println();
              System.out.format("InvOpen: %d\n", openCasesCount);
          }    

          st.close();
          
          st3.close();
        }
        catch (Exception e)
        {
          System.err.println("Got an exception! ");
          System.err.println(e.getMessage());
        }
        
        //sql get the count of open cases
        //show on label
        homePane.add(openCasesLabel);
        openCasesLabel.setText("Number of Open Cases: ");
        openCasesLabel.setBounds(390, 30, 150, 30);
        openCasesLabel.setForeground(Color.white);
        homePane.add(openCaseDispNumLabel);
        openCaseDispNumLabel.setText(Integer.toString(openCasesCount));//("#");
        openCaseDispNumLabel.setBounds(540, 30, 200, 30);
        openCaseDispNumLabel.setForeground(Color.orange);
        
        homePane.add(lastLoginLabel);
        lastLoginLabel.setText("Last Login:");
        lastLoginLabel.setBounds(390,110,200,30);
        lastLoginLabel.setForeground(Color.white);
        homePane.add(lastLoginDispLabel);
        lastLoginDispLabel.setBounds(460, 110, 200, 30);
        lastLoginDispLabel.setForeground(Color.orange);

        homePane.add(caseCountLabel);
        caseCountLabel.setText("YTD Total of Open Cases:");
        caseCountLabel.setBounds(390, 70, 200,30);
        caseCountLabel.setForeground(Color.white);
        homePane.add(caseCountDispNumLabel);
        caseCountDispNumLabel.setText(Integer.toString(totalCasesCount));
        caseCountDispNumLabel.setBounds(542, 70, 200, 30);
        caseCountDispNumLabel.setForeground(Color.orange);
        homePane.setBackground(Color.darkGray);
        
        Font fn = new Font("Serif", Font.BOLD, 100);
        JLabel logo = new JLabel();
        homePane.add(logo);
        logo.setText("CSI ~ S.L.");
        logo.setBounds(230,180,500,150);
        logo.setForeground(Color.GRAY);
        logo.setFont(fn);
        
        
        setSize(750,400);//size of window

        //Set the window's location.
        setLocation(300, 15);
        
    }
    
}
